#!/bin/bash

DBHOST=""
DBPORT="3306"
DBUSER=""
DBPASS=""

CONN="mysql -u${DBUSER} -p${DBPASS} -h${DBHOST} -P${DBPORT} --default-character-set=utf8"

OP_DB_LIST="$(echo "show databases" |$CONN 2>/dev/null |egrep -v "information_schema|mysql|performance_schema|sys" |grep "_bak")"

echo "此次操作涉及的库名:[$OP_DB_LIST]"

rename_db2del(){
    for dbname in ${OP_DB_LIST} ; do
        origin_dbname=${dbname/_bak/}  # 去除_bak后缀
        del_dbname="del_${origin_dbname}" 
        
        echo "create database ${del_dbname};" |$CONN 2>/dev/null
        test $? -ne 0 && {
            echo "创建库 $del_dbname 失败"
            exit 1
        }

        echo "select table_name from information_schema.tables where table_schema=\"${origin_dbname}\" and table_type='base table';" |$CONN 2>/dev/null |egrep -v "table_name" |while read table_name ;do
            echo "RENAME TABLE ${origin_dbname}.${table_name} TO ${del_dbname}.${table_name};" |$CONN 2>/tmp/.ttt
            test $? -ne 0 && {
                echo "rename table FAIL =${origin_dbname}.${table_name}="
                cat /tmp/.ttt && rm -f /tmp/.ttt
                exit 1
            }
        done
        echo "重命名  $origin_dbname  -->  $del_dbname  操作完成"
    done

}

rename_bak2normal(){
    for dbname in ${OP_DB_LIST}; do
        origin_dbname=${dbname/_bak/}

        echo "select table_name from information_schema.tables where table_schema=\"${dbname}\" and table_type='base table';" |$CONN 2>/dev/null |egrep -v "table_name" |while read table_name ;do
            echo "RENAME TABLE ${dbname}.${table_name} TO ${origin_dbname}.${table_name};" |$CONN 2>/tmp/.ttt
            test $? -ne 0 && {
                echo "rename table FAIL =${dbname}.${table_name}="
                cat /tmp/.ttt && rm -f /tmp/.ttt
                exit 1
            }
        done
        echo "重命名  $dbname -->  $origin_dbname  操作完成"
    done
}

delete_old(){
    for dbname in ${OP_DB_LIST}; do
        tables="$(echo "select table_name from information_schema.tables where table_schema=\"${dbname}\" and table_type='base table';" |$CONN 2>/dev/null |egrep -v "table_name")"
        if [[ "${tables}" == "" ]]; then
            echo "DROP DATABASE $dbname;" |$CONN 2>/dev/null
            test $? -eq 0 && echo "删除  $dbname  完成"
        fi
    done
}

run(){
    rename_db2del
    rename_bak2normal
    delete_old
}

run
